package com.xpec.c4.game.utility.dbtool;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;

import com.xpec.c4.game.utility.GameUtility;

/**
 * 總共三個參數: </br>
 * 參數1 : 'Source Path' 已經修改過的 create table 語法 </br>
 * 參數2 : 'Target Path' 修改前的 create table 語法 </br>
 * 參數3 : 'Output Path' 填了會輸出文字檔，沒填會秀在 console 上 </br>
 * @author TW16028
 */
public class AlterTableCreater {	
	
	private static final String FILTER_DROP_TABLE_IF_EXISTS = "DROP TABLE IF EXISTS";
	
	private static final String FILTER_LINE = "-";
	
	private static final String FILTER_SHARP = "#";
	
	private static final String FILTER_COMMAND = "/";
	
	private static final String START_WORD_CREATE = "CREATE TABLE";
	
	private static final String START_WORD_INDEX = "KEY";
	
	private static final String START_WORD_PRIMARY = "PRIMARY";
	
	private static final String NAME_SIGN = "`";
	
	private static final String END_SIGN = ";";
	
	//ALTER TABLE `lnsn_gamelog_1`
	private static final String ALTER_TABLE_COMMAND = "ALTER TABLE `%s` ";
	
	
	/**
	 * 	ADD COLUMN `trialpourBase`           DECIMAL(19,2) NULL 	DEFAULT '0.00' 	AFTER `trialwaterpool_range`,
	 *	ADD COLUMN `trialpourStart`          DECIMAL(19,2) NULL 	DEFAULT '0.00' 	AFTER `trialpourBase`,
	 *  ADD COLUMN `trialpourState`          VARCHAR(255)  NULL 	DEFAULT NULL 	AFTER `trialpourStart`,
	 *	ADD COLUMN `trialpourTotal`          DECIMAL(19,2) NULL 	DEFAULT '0.00' 	AFTER `trialpourState`,
	 *	ADD COLUMN `trialwaterpool_feedback` INT(11)       NOT NULL 				AFTER `trialpourTotal`,
	 *	ADD COLUMN `trialwaterpool_range`    VARCHAR(255)  NULL 	DEFAULT NULL 	AFTER `trialwaterpool_feedback`;
	 *
	 *	`trialpourBase` 			DECIMAL(19,2) NULL DEFAULT '0.00',
	 *	`trialpourStart` 			DECIMAL(19,2) NULL DEFAULT '0.00',
	 *	`trialpourState` 			VARCHAR(255)  NULL DEFAULT NULL COLLATE 'utf8_bin',
	 *	`trialpourTotal` 			DECIMAL(19,2) NULL DEFAULT '0.00',
	 *	`trialwaterpool_feedback` 	INT(11) 	  NOT NULL,
	 *	`trialwaterpool_range` 		VARCHAR(255)  NULL DEFAULT NULL COLLATE 'utf8_bin',
	 */
	//ADD COLUMN `trialpourBase` DECIMAL(19,2) NULL DEFAULT '0.00' 
	private static final String ADD_COLUMN_COMMAND = " ADD COLUMN %s ";
	
	//AFTER `rookie_waterpool_range`,
	private static final String AFTER_COLUMN = " AFTER `%s` ,";
	
	private static final String ADD_INDEX_COMMAND = " ADD %s ,";
	
	private static final String DROP_INDEX_COMMAND = " DROP INDEX %s ,";
	
	public static void main(String[] args) {
		
//		String sourcePath = "C:\\Users\\TW16028\\Documents\\Tencent Files\\2394238695\\FileRecv\\source_google.txt";
		
		//來源(已修改的)
		String sourcePath = args[0];
		
		System.out.println("Load Source Start");
		LinkedHashMap<String, TableInfos> sourceData = loadFile(sourcePath);
		System.out.println("Load Source Finish");
		
//		String targetPath = "C:\\Users\\TW16028\\Documents\\Tencent Files\\2394238695\\FileRecv\\target_japan.txt";
		
		//對象(未修改的)
		String targetPath = args[1];
		System.out.println("Load Target Start");
		LinkedHashMap<String, TableInfos> targetData = loadFile(targetPath);
		System.out.println("Load Target Finish");
		
		System.out.println("Compare Start");
		LinkedHashMap<String, AlterCommandInfo> changes = compare(sourceData, targetData);
		System.out.println("Compare Finish");
		
		System.out.println("Get Output Start");
		String output = getOutputString(changes);
		System.out.println("Get Output Finish");
		
		if (args.length > 2) {
//			String outputPath = "C:\\Users\\TW16028\\Documents\\Tencent Files\\2394238695\\FileRecv\\alterTable.txt";
			String outputPath = args[2];
			writeFile(outputPath, output);
		} else {
			System.out.println(output);
		}
	}
	
	/**
	 * 讀檔
	 * @param path
	 * @return
	 */
	@SuppressWarnings("resource")
	private static LinkedHashMap<String, TableInfos> loadFile(String path){
		
		FileReader fileReader = null;
		
		//table name / filedName / info
		LinkedHashMap<String, TableInfos> tableInfos = new LinkedHashMap<String, TableInfos>();
		
		try {
			fileReader = new FileReader(path);
			
            BufferedReader bufferedReader = new BufferedReader(fileReader);
            
            StringBuilder builder = new StringBuilder();
            
            String tableName = null;
            
			while (bufferedReader.ready()) {

				String line = bufferedReader.readLine();
				if (!GameUtility.isHaveString(line) ||  line.startsWith(FILTER_LINE) || line.startsWith(FILTER_SHARP) || line.startsWith(FILTER_COMMAND) || line.startsWith(FILTER_DROP_TABLE_IF_EXISTS)) {
					// 空字串 or - or #
					continue;
				}
				
				TableInfos info = null;
				if (tableName != null) {
					info = tableInfos.get(tableName);
				} else {
					builder.setLength(0);
				}
				
				builder.append(line);

				String[] lineSplit = line.split(NAME_SIGN);
				if (line.startsWith(START_WORD_CREATE)) {
					//開頭
					tableName =  lineSplit[1];
					info = new TableInfos(tableName);
					tableInfos.put(tableName, info);
					builder.append(System.getProperty("line.separator"));
					
				} else if (line.trim().startsWith(START_WORD_INDEX) || line.trim().startsWith(START_WORD_PRIMARY)) {
					//index or pk
					LinkedHashMap<String, String> indexs = info.getIndexs();
					String indexName = lineSplit[1];
					indexs.put(indexName, line);
					builder.append(System.getProperty("line.separator"));

				} else if (line.endsWith(END_SIGN)) {
					// 結尾
					info.setCreateTableCommand(builder.toString());
					tableName = null;
				} else {
					//column
					LinkedHashMap<String, String> columns = info.getColumns();
					String columnName = lineSplit[1];
					columns.put(columnName, line);
					builder.append(System.getProperty("line.separator"));
				}
			}
		}
		catch (IOException e) {
			e.printStackTrace();
		}finally{
			if (fileReader != null) {
				try {
					fileReader.close();
				}
				catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return tableInfos;
	}

	/**
	 * 輸出的文字
	 * @param changes
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	private static String getOutputString(LinkedHashMap<String, AlterCommandInfo> changes){
		
		StringBuilder builder = new StringBuilder();
		
		Iterator<?> it = changes.entrySet().iterator();
		while ( it.hasNext()) {
			Map.Entry element = (Map.Entry)  it.next();
			
			String tableName = (String)element.getKey();
			AlterCommandInfo info = (AlterCommandInfo) element.getValue();
			
			if(info.isCreateTable()){
				builder.append(info.getCreateTableCommand());
				builder.append(System.getProperty("line.separator"));
				builder.append(System.getProperty("line.separator"));
				continue;
			}
			
			LinkedHashMap<String, AlterColumnInfo> columns = info.getAlterColumns();

			builder.append(String.format(ALTER_TABLE_COMMAND, tableName)); 
			builder.append(System.getProperty("line.separator"));
			
			Iterator<?> itColumn = columns.entrySet().iterator();
			while ( itColumn.hasNext()) {
				Map.Entry entry = (Map.Entry) itColumn.next();
				AlterColumnInfo alterInfo = (AlterColumnInfo)entry.getValue();
				
				if(alterInfo.isAddColumn()){
					//ADD COLUMN
					String addCommand = createAddColumnCommand(alterInfo);
					builder.append(addCommand);
					builder.append(System.getProperty("line.separator"));
				} else {
					// CHANGE COLUMN 先不做跳過
				}
				
			}
			
			LinkedHashMap<String, AlterIndexInfo> indexs = info.getAlterIndexs();
			Iterator<?> itIndex = indexs.entrySet().iterator();
			while (itIndex.hasNext()) {
				Map.Entry entry = (Map.Entry) itIndex.next();
				AlterIndexInfo alterInfo = (AlterIndexInfo) entry.getValue();

				String addCommand = createAddIndexCommand(alterInfo);
				builder.append(addCommand);
				builder.append(System.getProperty("line.separator"));
			}
			
			//如果後面已經沒了，最後結尾要把 ','換成;
			int singIndex = builder.lastIndexOf(",");
			builder.replace(singIndex, singIndex + 1, ";");
			builder.append(System.getProperty("line.separator"));
		}
		
		return builder.toString();
	}
	
	/**
	 * 比較 source & target
	 * @param source
	 * @param target
	 * @return
	 */
	@SuppressWarnings({ "rawtypes" })
	private static LinkedHashMap<String, AlterCommandInfo> compare(LinkedHashMap<String, TableInfos> source, LinkedHashMap<String, TableInfos> target) {
		
		LinkedHashMap<String, AlterCommandInfo> commandInfos = new LinkedHashMap<String, AlterCommandInfo>();
		Iterator<?> it = source.entrySet().iterator();
		while (it.hasNext()) { 
			//一個table 一個tabel比較
			
			Map.Entry entry = (Map.Entry) it.next();
			String tableName = (String) entry.getKey();
			TableInfos sourceInfo = (TableInfos) entry.getValue();
			
			//紀錄不一樣的東西
			AlterCommandInfo commandInfo = new AlterCommandInfo(tableName);

			if (target.containsKey(tableName)) {
				
				TableInfos targetInfo = target.get(tableName);
				
				if (GameUtility.safeStringEquals(sourceInfo.getCreateTableCommand(), targetInfo.getCreateTableCommand())) {
					// 代碼一樣
					continue;
				}

				commandInfos.put(tableName, commandInfo);
				
				// 看看column
				LinkedHashMap<String, String> sourceColumns = sourceInfo.getColumns();
				Iterator<?> columnIt = sourceColumns.entrySet().iterator();
				String lastColumnName = null;
				while (columnIt.hasNext()) {
					Map.Entry columnEntry = (Map.Entry) columnIt.next();
					// 欄位名稱
					String columnName = (String) columnEntry.getKey();
					
					// 欄位
					String sourceColumnCommand = (String) columnEntry.getValue();

					// 從target取出來
					String targetColumnCommand = targetInfo.getColumn(columnName);
					if (targetColumnCommand == null) {
						
						AlterColumnInfo alterInfo = new AlterColumnInfo();
						alterInfo.setAfterColumn(lastColumnName);
						alterInfo.setCommand(sourceColumnCommand);
						alterInfo.setAddColumn(true);
						
						// 沒有這個欄位，要alter
						commandInfo.getAlterColumns().put(columnName, alterInfo);
						
						lastColumnName = columnName;
						continue;
					}

 					if (GameUtility.safeStringEquals(sourceColumnCommand.replace(",", "").trim(), targetColumnCommand.replace(",", "").trim())) {
						lastColumnName = columnName;
						// 一樣
						continue;
					}
 					//先不做 change column 
 					
					// 有這個欄位，要比較 不一樣要蓋過去
//					AlterColumnInfo alterInfo = new AlterColumnInfo();
//					alterInfo.setAfterColumn(lastColumnName);
//					alterInfo.setCommand(sourceColumnCommand);
//					
//					commandInfo.getAlterColumns().put(columnName, alterInfo);
//					lastColumnName = columnName;
				}
				
				// 看看Index
				LinkedHashMap<String, String> sourceIndexs = sourceInfo.getIndexs();
				Iterator<?> indexIt = sourceIndexs.entrySet().iterator();
				while (indexIt.hasNext()) {
					Map.Entry columnEntry = (Map.Entry) indexIt.next();
					// Index 名稱
					String indexName = (String) columnEntry.getKey();
					
					// 欄位
					String sourceIndexCommand = (String) columnEntry.getValue();
					
					// 從target取出來
					String targetIndexCommand = targetInfo.getIndex(indexName);
					if (targetIndexCommand == null) {
						//沒有這個 Index，要ADD
						
						AlterIndexInfo alterInfo = new AlterIndexInfo();
						alterInfo.setCommand(sourceIndexCommand);
						alterInfo.setAddNewIndex(true);
						
						commandInfo.alterIndexs.put(indexName, alterInfo);
						continue;
					}
					
					if (GameUtility.safeStringEquals(sourceIndexCommand.replace(",", "").trim(), targetIndexCommand.replace(",", "").trim())) {
						// 一樣
						continue;
					}
					
					// 有這個欄位，要比較 不一樣要蓋過去
					AlterIndexInfo alterInfo = new AlterIndexInfo();
					alterInfo.setCommand(sourceIndexCommand);
					
					commandInfo.getAlterIndexs().put(indexName, alterInfo);					
				}
				if (GameUtility.getMapSize(commandInfo.getAlterColumns()) <= 0 && GameUtility.getMapSize(commandInfo.getAlterIndexs()) <= 0) {
					//沒更新到的要移除
					commandInfos.remove(tableName);
				}
			} else { //if (target.containsKey(tableName)) {
				// 沒有這個tabel，就是要insert
				commandInfos.put(tableName, commandInfo);
				commandInfo.setCreateTableCommand(sourceInfo.getCreateTableCommand());
				continue;
			}
		}
		
		return commandInfos;
	}
	
	/**
	 * 寫檔
	 * @param path
	 * @param outputStr
	 */
	private static void writeFile(String path, String outputStr){
		FileWriter fw = null;
		try {
			fw = new FileWriter(path, false);
			fw.write(outputStr);
		}
		catch (IOException e) {
			e.printStackTrace();
		}
		finally {
			if (fw != null) {
				try {
					fw.close();
				}
				catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	/**
	 * 產生 add Column 語法
	 * @param info
	 * @return
	 */
	private static String createAddColumnCommand(AlterColumnInfo info){
		
		//去頭尾空白
		String insertCommand = info.getCommand().trim();
		// 替換最後一個','成空白
		insertCommand = insertCommand.lastIndexOf(",") + 1 == insertCommand.length() ? insertCommand.substring(0, insertCommand.lastIndexOf(",")) : insertCommand;
		
		String afterColumn = info.getAfterColumn();
		
		StringBuilder builder = new StringBuilder();
		//ADD COLUMN `trialpourBase` DECIMAL(19,2) NULL DEFAULT '0.00' 
		builder.append(String.format(ADD_COLUMN_COMMAND, insertCommand));		
		
		//AFTER `rookie_waterpool_range`
		builder.append(String.format(AFTER_COLUMN, afterColumn));
		
		return builder.toString();
	}
	
	/**
	 * 產生 add index 語法
	 * @param info
	 * @return
	 */
	private static String createAddIndexCommand(AlterIndexInfo info){
		
		//去頭尾空白，再替換','成空白
		String indexCommand = info.getCommand().trim();
		// 替換最後一個','成空白
		indexCommand = indexCommand.lastIndexOf(",") == indexCommand.length() ? indexCommand.substring(indexCommand.lastIndexOf(","), indexCommand.lastIndexOf(",") + 1) : indexCommand;
		StringBuilder builder = new StringBuilder();
		boolean isAddNew = info.isAddNewIndex();
		if(isAddNew){
			builder.append(String.format(ADD_INDEX_COMMAND, indexCommand));
			
		}else {
			builder.append(String.format(DROP_INDEX_COMMAND,indexCommand.split(" ")[1]));
			builder.append(System.getProperty("line.separator"));
			builder.append(String.format(ADD_INDEX_COMMAND, indexCommand));			
		}
		
		return builder.toString();
	}
	
	
}
